﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using IToursDataService;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using IToursDataService.Models;

namespace ToursDataService
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“GuideArrangeCtrl”。
    public class GuideArrangeCtrl : IGuideArrangeCtrl
    {
        /// <summary>
        /// 获取指定团的导游安排数据信息
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public DataTable GetGuideArrangeData(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //string sql = string.Format(@"SELECT g.*,c.Phone FROM cic_GuideArrange g,bas_Ciceroni c WHERE TourgroupID='{0}'AND g.CiceroniID = c.CiceroniID AND g.OwnerProviderID='{1}'", tourgroupId, ApplicationHelper.CurrentLoginProviderNameAndID[1]);
            string sql = string.Format(@"SELECT g.*,p.ProviderName,c.Phone FROM cic_GuideArrange AS g LEFT OUTER JOIN dbo.bas_Ciceroni AS c ON g.CiceroniID = c.CiceroniID LEFT OUTER JOIN dbo.bas_Provider AS p ON c.OwnerProviderID = p.ProviderID
                                         WHERE TourgroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取所属供应商的导游安排信息，专为客户端各地接社使用
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <param name="ownerProviderID"></param>
        /// <returns></returns>
        public DataTable GetGuideArrangeData(string tourgroupId, int ownerProviderID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //string sql = string.Format(@"SELECT g.*,c.Phone FROM cic_GuideArrange g,bas_Ciceroni c WHERE TourgroupID='{0}'AND g.CiceroniID = c.CiceroniID AND g.OwnerProviderID='{1}'", tourgroupId, ApplicationHelper.CurrentLoginProviderNameAndID[1]);
            string sql = string.Format(@"SELECT g.*,c.Phone FROM cic_GuideArrange AS g LEFT OUTER JOIN dbo.bas_Ciceroni AS c ON g.CiceroniID = c.CiceroniID LEFT OUTER JOIN dbo.bas_Provider AS p ON c.OwnerProviderID = p.ProviderID
                                         WHERE TourgroupID='{0}' AND g.OwnerProviderID='{1}'", tourgroupId, ownerProviderID);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
//        public DataTable GetGuideArrangeData(DateTime _StartDate, DateTime _EndDate)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            DbCommand dbCommand;
//            string sql = string.Format(@"SELECT [GuideArrangeID], [TourgroupID], [StartDate],[Calendars], [EndDate], [CiceroniID], [CiceroniName],
//                                                [Notified], [IsAccompany],[HasEvent], [Remark]  FROM cic_GuideArrange 
//                                                 WHERE  (datediff(minute,StartDate,'{0}')>=0  
//                                                 AND datediff(minute,EndDate,'{0}')<=0) 
//                                                 OR (datediff(minute,StartDate,'{1}')>=0  
//                                                 AND datediff(minute,EndDate,'{1}')<=0) 
//                                                 OR (datediff(minute,StartDate,'{0}')<=0  
//                                                 AND datediff(minute,EndDate,'{1}')>=0)",
//                                                 _StartDate.ToString("yyyy-MM-dd HH:mm"),
//                                                 _EndDate.ToString("yyyy-MM-dd HH:mm"));
//            dbCommand = db.GetSqlStringCommand(sql);

//            DataSet ds = db.ExecuteDataSet(dbCommand);
//            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
//            {
//                DateTime start, end;
//                //循环修改时间段信息，用于返回后着色GridView的导游安排Cell
//                foreach (DataRow dr in ds.Tables[0].Rows)
//                {
//                    start = DateTime.Parse(DateTime.Parse(dr["StartDate"].ToString()).ToString("yyyy-MM-dd"));
//                    end = DateTime.Parse(DateTime.Parse(dr["EndDate"].ToString()).ToString("yyyy-MM-dd"));

//                    if (_StartDate == end)
//                    {
//                        dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = _StartDate.ToString("yyyy-MM-dd");
//                    }
//                    if (_StartDate > start && _EndDate > end)
//                    {
//                        dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = end.ToString("yyyy-MM-dd");
//                    }
//                    if (_StartDate < start && _EndDate > end)
//                    {
//                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = end.ToString("yyyy-MM-dd");
//                    }
//                    if (_StartDate < start && _EndDate < end)
//                    {
//                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = _EndDate.ToString("yyyy-MM-dd");
//                    }
//                    if (start == _EndDate)
//                    {
//                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = start.ToString("yyyy-MM-dd");
//                    }
//                    if (_StartDate > start && _EndDate < end)
//                    {
//                        dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = _EndDate.ToString("yyyy-MM-dd");
//                    }
//                    if (_StartDate == start && _EndDate == end)
//                    {
//                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
//                        dr["EndDate"] = end.ToString("yyyy-MM-dd");
//                    }
//                }
//                return ds.Tables[0];
//            }
//            else
//                return null;
//        }
        /// <summary>
        /// 根据给定的上下团时间取得未安排的导游
        /// </summary>
        /// <param name="startDateTime"></param>
        /// <param name="endDateTime"></param>
        /// <returns></returns>
//        public DataTable GetGuideArrangeData(string cityname, string language, int statusIndex, DateTime startDateTime, DateTime endDateTime, string loginProviderID)
//        {

//            Database db = DatabaseFactory.CreateDatabase();

//            DbCommand dbCommand;
//            string sql = "";
//            switch (statusIndex)
//            {
//                case 0://待安排
//                    sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{2}%' AND CiceroniLanguage like '%{3}%' AND ciceroniid NOT IN 
//                                         (SELECT DISTINCT ciceroniid  FROM cic_GuideArrange 
//                                         WHERE  (datediff(minute,StartDate,'{0}')>=0  
//                                         AND datediff(minute,EndDate,'{0}')<=0) 
//                                         OR (datediff(minute,StartDate,'{1}')>=0  
//                                         AND datediff(minute,EndDate,'{1}')<=0) 
//                                         OR (datediff(minute,StartDate,'{0}')<=0  
//                                         AND datediff(minute,EndDate,'{1}')>=0))  AND OwnerProviderID='{4}' ORDER BY CiceroniLevel Desc",
//                                         startDateTime.ToString("yyyy-MM-dd HH:mm"),
//                                         endDateTime.ToString("yyyy-MM-dd HH:mm"),
//                                         cityname,
//                                         language,
//                                         loginProviderID);
//                    break;
//                case 1://已安排
//                    sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{2}%' AND CiceroniLanguage like '%{3}%' AND ciceroniid IN 
//                                         (SELECT DISTINCT ciceroniid  FROM cic_GuideArrange 
//                                         WHERE  (datediff(minute,StartDate,'{0}')>=0  
//                                         AND datediff(minute,EndDate,'{0}')<=0) 
//                                         OR (datediff(minute,StartDate,'{1}')>=0  
//                                         AND datediff(minute,EndDate,'{1}')<=0) 
//                                         OR (datediff(minute,StartDate,'{0}')<=0  
//                                         AND datediff(minute,EndDate,'{1}')>=0))  AND OwnerProviderID='{4}'  ORDER BY CiceroniLevel Desc",
//                                         startDateTime.ToString("yyyy-MM-dd HH:mm"),
//                                         endDateTime.ToString("yyyy-MM-dd HH:mm"),
//                                         cityname,
//                                         language,
//                                         loginProviderID);
//                    break;
//                case 2://全部
//                    sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{0}%' AND CiceroniLanguage like '%{1}%'  AND OwnerProviderID='{2}'   ORDER BY CiceroniLevel Desc", cityname, language, loginProviderID);
//                    break;
//                default:
//                    break;
//            }

//            dbCommand = db.GetSqlStringCommand(sql);

//            DataSet ds = db.ExecuteDataSet(dbCommand);
//            if (ds.Tables.Count > 0)
//                return ds.Tables[0];
//            else
//                return null;
//        }
        /// <summary>
        /// 根据指定日期和导游ID获取导游安排表的日历Calendars内容和主键GuideArrangeID值
        /// </summary>
        /// <param name="yMd"></param>
        /// <param name="ciceroniId">导游ID</param>
        /// <returns></returns>
        //public DataTable GetGuideArrangeData(string yMd, string ciceroniId)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT [GuideArrangeID],[Calendars] FROM cic_GuideArrange WHERE convert(varchar(10),[StartDate],121)<='{0}' AND '{1}'<=convert(varchar(10),[EndDate],121) AND  CiceroniID={2}", yMd, yMd, ciceroniId);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;

        //}
        /// <summary>
        /// 获取指定团的导游信息
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public DataTable GetGuideInfoForPrint(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT cic_GuideArrange.StartDate,bas_Ciceroni.CiceroniName,bas_Ciceroni.Phone,bas_Ciceroni.CiceroniID FROM cic_GuideArrange,bas_Ciceroni WHERE TourgroupID='{0}' and cic_GuideArrange.CiceroniID = bas_Ciceroni.CiceroniID", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 检测导游是否存在已经安排的信息
        /// </summary>
        /// <param name="ciceroniId"></param>
        /// <returns></returns>
        //public bool CheckExistGuideArrangeInfo(string ciceroniId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    DbCommand dbCommand;

        //    dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT CiceroniID FROM cic_GuideArrange WHERE CiceroniID='{0}'", ciceroniId.Trim()));

        //    object obj = db.ExecuteScalar(dbCommand);

        //    return obj != null;
        //}

        /// <summary>
        /// 更新导游安排数据中的‘是否通知’和说明信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int UpdateGuideArrangeData(GuideArrangeEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @" UPDATE [cic_GuideArrange]
                            SET [IsAccompany]=@IsAccompany, [Remark]=@Remark,[StartDate]=@StartDate,[EndDate]=@EndDate 
                            WHERE GuidearrangeID=@GuidearrangeID";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "GuidearrangeID", DbType.Int32, entity.GuideArrangeID);
            db.AddInParameter(dbCommand, "IsAccompany", DbType.Int32, entity.IsAccompany == true ? 1 : 0);
            db.AddInParameter(dbCommand, "Remark", DbType.String, entity.Remark);
            db.AddInParameter(dbCommand, "StartDate", DbType.String, entity.StartDate);
            db.AddInParameter(dbCommand, "EndDate", DbType.String, entity.EndDate);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新导游安排通知
        /// </summary>
        /// <param name="guideArrangeId"></param>
        /// <param name="notified"></param>
        //public int UpdateGuideArrangeData(int guideArrangeId, int notified)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = @" UPDATE [cic_GuideArrange] SET [Notified]=@Notified WHERE GuidearrangeID=@GuidearrangeID";

        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    db.AddInParameter(dbCommand, "GuidearrangeID", DbType.Int32, guideArrangeId);
        //    db.AddInParameter(dbCommand, "Notified", DbType.Int32, notified);

        //    return db.ExecuteNonQuery(dbCommand);
        //}
        /// <summary>
        /// 增加导游安排
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int CreateGuideArrangeData(List<GuideArrangeEntity> entities)//GuideArrangeEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder sql = new StringBuilder();
            foreach (var entity in entities)
            {
                sql.AppendFormat(@"INSERT INTO cic_GuideArrange ([TourgroupID], [StartDate],[Calendars],[EndDate], [CiceroniID], [CiceroniName], [Notified], 
                                                                       [HasEvent], [Remark],[IsAccompany],[OwnerProviderID])
                                                                       VALUES('{0}','{1}','{2}','{3}',{4},'{5}', {6},{7},'{8}',{9},'{10}')",
                                                                        entity.TourgroupID,
                                                                        entity.StartDate,
                                                                        entity.Calendars,
                                                                        entity.EndDate,
                                                                        entity.CiceroniID,
                                                                        entity.CiceroniName,
                                                                        entity.Notified == true ? 1 : 0,
                                                                        entity.HasEvent,
                                                                        entity.Remark,
                                                                        entity.IsAccompany == true ? 1 : 0,
                                                                        entity.OwnerProviderID);
            }
            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }
        /// <summary>
        /// 删除指定导游安排编号的数据，同时更新团HasGuide
        /// </summary>
        /// <param name="guideArrangeId"></param>
        public void DeleteGuideArrangeData(string guideArrangeId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            //1、根据导游安排ID,获取团ID
            //object tourgroupId = db.ExecuteScalar(CommandType.Text,string.Format("SELECT tourgroupid FROM cic_guidearrange WHERE guidearrangeId={0}",guideArrangeId));

            //2、删除
            string sql = string.Format(@"DELETE FROM cic_GuideArrange WHERE GuideArrangeID={0}", guideArrangeId);
            db.ExecuteNonQuery(CommandType.Text, sql);

            //3、判断导游安排表中是否还有1中所获取团号已安排导游，没有则将团表中HasGuid置为false
            //sql = string.Format(@"SELECT COUNT(*) FROM cic_guidearrange WHERE tourgroupid='{0}'", tourgroupId);
            //if (db.ExecuteScalar(CommandType.Text, sql).ToString() == "0")
            //    TeamGroupCtrl.UpdateTourGroupHasGuide(tourgroupId.ToString(),false);
        }
    }
}
